#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.switch_code import *
import sys
import re
reload(sys)
sys.setdefaultencoding('utf-8')

#mysql
def set_order_conn():    
    conn = HandleMysql('emclottery.db.58dns.org','58885','emclottery_rw','P3%J&ePihR','dbwww58com_emclottery')
    conn.connect()
    return conn

#mysql - stats
def set_stats_conn():    
    conn = HandleMysql('10.5.20.16','58885','ec_stat','L#c8UM5RGh5C','dbwww58com_58_stats')
    conn.connect()
    return conn

def get_order_data(date):
    conn = set_order_conn()
    stat_conn = set_stats_conn()    
    stat_table = "caipiao_kpi_day"
    
    #print stat_table
    
    query = "select lotid,lotteryname,count(case when date(createtime)='%s' then 1 end),count(case when orderstate>2 and date(paydate)='%s' then 1 end),ifnull(sum(case when orderstate>2 then lotmoney else 0 end),0),count(case when lotstate=2 then 1 end),ifnull(sum(case when lotstate=2 then lotmoney else 0 end),0),count(distinct case when orderstate>2 then buyerid end) from t_lotteryorder where date(createtime)='%s' group by lotid;"%(date,date,date)
    items = conn.get_results(query)
    for item in items:
        lottery_id = item[0]
        lottery_name = item[1]
        
        order_count = item[2]
        order_pay_count = item[3]
        pay_amount = item[4]
        order_refund_count = item[5]
        order_refund_amount = item[6]
        buy_user_count = item[7]
    
        #print order_count
        pay_commission = 0
        if pay_amount > 3000000:
            pay_commission = pay_amount*0.085
        else:
            pay_commission = pay_amount*0.08
        
        pv = 0
        visits =0
        
        param = [date,lottery_id,lottery_name,order_count,order_refund_count,order_refund_amount,order_pay_count,pay_amount,pay_commission,buy_user_count,pv,visits]
        #print param
        stat_conn.insert_record(stat_table,param)
    query = "insert into %s(stat_date,lottery_id,lottery_name) values('%s',999,'other');"%(stat_table,date)
    stat_conn.execute_sql(query)
    conn.close()
    stat_conn.close()

def get_caipiao_items(date):
    stat_conn = set_stats_conn()    
    stat_table = "caipiao_items"
    
    query = "truncate table %s"%stat_table
    stat_conn.execute_sql(query)
    
    query = "SELECT lottery_id,lottery_name  FROM caipiao_kpi_day group by lottery_id;"
    items = stat_conn.get_results(query)
    for item in items:
        lottery_id = item[0]
        lottery_name = item[1]
        
        param = [lottery_id,lottery_name]
        #print param
        stat_conn.insert_record(stat_table,param)
    #query = "insert into %s(lottery_id,lottery_name) values(999,'other');"%stat_table
    #stat_conn.execute_sql(query)
    query = "update `caipiao_kpi_day` a inner join caipiao_flux_day b on a.stat_date=b.stat_date  and a.lottery_name=b.lottery_name set a.pv=b.pv,a.visits=b.visits where a.stat_date='%s';"%date
    stat_conn.execute_sql(query)
    stat_conn.close()
#logic
def execute(date):

    try:
        # pass
        get_order_data( date )
    except Exception,e:
        print 'get_order_data error:',e
    try:
        pass
        get_caipiao_items( date )
    except Exception,e:
        print 'get_caipiao_items error:',e
#
def main():

   #date = '2012-05-28'
   #execute(date)   
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])
   stat_date=start_date
   while stat_date>=start_date and stat_date<=end_date:
        print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
        #
        execute( stat_date) 
        print "The program of the date of %s is end at %s " %(stat_date,get_now())
        stat_date=var_date(stat_date)


if __name__=='__main__':
    main()
